using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using CoreLibrary.Date;
using CoreLibrary.Sql;

namespace CoreLibrary.Messaging.Sql {

    public class ListMessages : ISqlQuery<List<Message>> {

        private readonly string _tableName;
        private readonly ListMessageQuery _query;

        public ListMessages(string tableName, ListMessageQuery query) {
            _tableName = tableName;
            _query = query;
        }

        public string GetSQL() {
            string statusParams = StringUtil.Join(", ", _query.MessageStatuses, true);

            return string.Format(@"
                select  *
                from ( select row_number() over ( order by date_scheduled {1}) as rownum, *
                          from   {0}
                          where  status in ({2}) and
                                 date_scheduled >= @scheduledFrom and
                                 date_scheduled <= @scheduledTo 
                        ) as rowconstrainedresult
                where rownum >= {3} and rownum < {4}
                order by rownum                       
            ",  _tableName, 
                _query.Asc ? Order.Asc.GetValue() : Order.Desc.GetValue(), 
                statusParams,
                _query.Offset, 
                _query.Offset + _query.Limit
            );
        }

        public void SetParameters(SqlCommand command) {
            command.Parameters.Add("@scheduledFrom", SqlDbType.DateTime).Value = DateUtil.ConvertMillisSinceEpochToDateTime(_query.ScheduledDateFrom, DateUtil.Epoch);
            command.Parameters.Add("@scheduledTo", SqlDbType.DateTime).Value = DateUtil.ConvertMillisSinceEpochToDateTime(_query.ScheduledDateTo, DateUtil.Year2050);
        }

        public List<Message> GetResult(SqlConnection conn, SqlDataReader result) {
            var messages = new List<Message>();
            if (result.HasRows) {
                while (result.Read()) {
                    messages.Add(GetMessage.ReadMessage(result));
                }
            }
            return messages;
        }
    }
}